--    Author    : ChenErHao
--    Name      : DM.T_MTH_DEP_RATE.HQL
--    Functions : 利率-存款利率分析
--    Purpose   :
--    Revisions or Comments
--    VER        DATE        AUTHOR           DESCRIPTION
--   ---------  ----------  ---------------  ------------------------------------
--    1.0        2019-01-23  ChenErHao       1.CREATE THE PROCEDURE
--


INSERT OVERWRITE TABLE DM.T_MTH_DEP_RATE PARTITION (FISCAL_MTH_DIMNSN_ID = '#V_DATA_MONTH#') 
  SELECT
        nvl(T1.BELONG_AREA_CODE,9999),
        T1.ORG_LVL_ID_2,
        nvl(T1.CUSTOMER_TYPE,9999),
        T1.DEPB_CATEGORY_ID_1,
        nvl(T1.LOAN_PERIOD_TYPE_ID,9999),
        nvl(SUM(T1.DEP_BALANCE),0)                DEP_BALANCE,      ---存款余额
        nvl(SUM(T1.DEP_BALANCE_RATE),0)           DEP_BALANCE_RATE, ---存款余额加权金额
        nvl(SUM(T1.DEP_BALANCE_LM),0)            DEP_BALANCE_LM, ---上月存款余额
        nvl(SUM(T1.DEP_BALANCE_RATE_LM),0)      DEP_BALANCE_RATE_LM, ---上月存款余额加权金额
        nvl(SUM(T1.DEP_BALANCE_AVG),0)            DEP_BALANCE_AVG, ---存款余额利率合计
        nvl(SUM(T1.STROKE_COUNT),0)               STROKE_COUNT, ---存款余额笔数
        nvl(SUM(T1.DEP_BALANCE_RATE_SUM_L),0)     DEP_BALANCE_RATE_SUM_L, ---上月存款余额利率合计
        nvl(SUM(T1.DEP_BALANCE_STROKE_COUNT_L),0) DEP_BALANCE_STROKE_COUNT_L, ---上月存款余额笔数
        nvl(SUM(T1.DEP_AMOUNT),0)               DEP_AMOUNT, ---存款发生额
        nvl(SUM(T1.DEP_AMOUNT_AMT),0)             DEP_AMOUNT_AMT, ---存款发生额加权金额
        nvl(SUM(T1.DEP_MTH_LOAN_AMOUNT),0)        DEP_MTH_LOAN_AMOUNT, ---上月存款发生额
        nvl(SUM(T1.DEP_MTH_LOAN_AMOUNT_AMT),0)    DEP_MTH_LOAN_AMOUNT_AMT, ---上月存款发生额加权金额
        nvl(SUM(T1.DEP_AMOUNT_RATE_SUM),0)      DEP_AMOUNT_RATE_SUM, ---存款发生额利率合计
        nvl(SUM(T1.DEP_AMOUNT_STROKE_COUNT),0)   DEP_AMOUNT_STROKE_COUNT, ---存款发生额笔数
        nvl(SUM(T1.DEP_AMOUNT_RATE_SUM_L),0)       DEP_AMOUNT_RATE_SUM_L, ---上月存款发生额利率合计
        nvl(SUM(T1.DEP_AMOUNT_STROKE_COUNT_L),0)    DEP_AMOUNT_STROKE_COUNT_L ---上月存款发生额笔数
  FROM
      (SELECT
         T1.BELONG_AREA_CODE,
         T1.ORG_LVL_ID_2,
         T1.CUSTOMER_TYPE,
         T1.DEPB_CATEGORY_ID_1,
         T1.LOAN_PERIOD_TYPE_ID,
         SUM(DEP_BALANCE)             DEP_BALANCE,  ---存款余额
         SUM(DEP_BALANCE_RATE)        DEP_BALANCE_RATE, ---存款余额加权金额
         0 DEP_BALANCE_LM,0 DEP_BALANCE_RATE_LM,
         SUM(DEP_BALANCE_AVG)         DEP_BALANCE_AVG, ---存款余额利率合计
         SUM(STROKE_COUNT)            STROKE_COUNT, ---存款余额笔数
         0 DEP_BALANCE_RATE_SUM_L,0 DEP_BALANCE_STROKE_COUNT_L,
         SUM(DEP_AMOUNT)              DEP_AMOUNT, ---存款发生额
         SUM(DEP_AMOUNT_AMT)          DEP_AMOUNT_AMT, ---存款发生额加权金额
         0 DEP_MTH_LOAN_AMOUNT,0 DEP_MTH_LOAN_AMOUNT_AMT,
         SUM(DEP_AMOUNT_RATE_SUM)     DEP_AMOUNT_RATE_SUM, ---存款发生额利率合计
         SUM(DEP_AMOUNT_STROKE_COUNT) DEP_AMOUNT_STROKE_COUNT, ---存款发生额笔数
         0 DEP_AMOUNT_RATE_SUM_L,0 DEP_AMOUNT_STROKE_COUNT_L
       FROM (SELECT
               BELONG_AREA_CODE,
               CASE WHEN B.ORG_LVL_ID_2 IS NULL THEN '9999'
                    WHEN B.ORG_LVL_ID_2='' THEN '9999'
                    ELSE B.ORG_LVL_ID_2 END ORG_LVL_ID_2,
               CUSTOMER_TYPE,
               CASE WHEN C.DEPB_CATEGORY_ID_1 IS NULL THEN '9999'
                    WHEN C.DEPB_CATEGORY_ID_1 ='' THEN '9999'
                    ELSE C.DEPB_CATEGORY_ID_1 END DEPB_CATEGORY_ID_1, ---存款产品类别
               LOAN_PERIOD_TYPE_ID,
               SUM(DEP_BALANCE)              DEP_BALANCE,
               SUM(LOAN_OCCURRENCE_LEVEL)    DEP_BALANCE_RATE, ---加权金额
               SUM(RATE_LEVEL)               DEP_BALANCE_AVG, ---利率水平
               SUM(STROKE_COUNT)             STROKE_COUNT, ---存款余额
               0                             DEP_AMOUNT, ---存款发生额
               0                             DEP_AMOUNT_AMT,
               0                             DEP_AMOUNT_RATE_SUM,
               0                             DEP_AMOUNT_STROKE_COUNT
           FROM
               EDW.DEPB A
           LEFT JOIN DMCODE.T_ORG_BIZ_LVL B ON CASE WHEN a.DATA_ORG_CODE IS NULL THEN CONCAT('org_code_hive',RAND()) else A.DATA_ORG_CODE end= B.ORG_ID  ---金融机构
           LEFT JOIN DMCODE.T_DEPB_CATEGORY C ON CASE WHEN a.PRODUCT_CATEGORY IS NULL THEN CONCAT('org_code_hive',RAND()) else A.PRODUCT_CATEGORY end= C.DEPB_CATEGORY_ID ---存款产品类别
           WHERE
                    DATA_DATE ='#V_DATA_DATE#'
           GROUP BY
                    A.BELONG_AREA_CODE  , B.ORG_LVL_ID_2   ,
                    CUSTOMER_TYPE, C.DEPB_CATEGORY_ID_1   , ---存款产品类别
                    LOAN_PERIOD_TYPE_ID

       UNION ALL

                SELECT
                            data_area_code BELONG_AREA_CODE,
                           DATA_ORG_CODE,
                            CUSTOMER_TYPE,
                            PRODUCT_CATEGORY,
                            LOAN_PERIOD_TYPE_ID, 
                    0 DEP_BALANCE,0 DEP_BALANCE_RATE,0 DEP_BALANCE_AVG,0 STROKE_COUNT,
                     DEP_AMOUNT  DEP_AMOUNT, ---存款发生额
                     DEP_AMOUNT_AMT  DEP_AMOUNT_AMT, ---存款发生额加权金额
                    0 DEP_AMOUNT_RATE_SUM, ---存款发生额利率合计
                    0 DEP_AMOUNT_STROKE_COUNT---存款发生额笔数
                 FROM  DM.MID_T_MTH_DEP_RATE_D011013 t
                 inner join DMCODE.T_ORG_BIZ_LVL a on t.DATA_ORG_CODE = a.ORG_LVL_ID_2  --因MID_T_MTH_DEP_RATE_D011013表中包含第三层数据，为不影响原来算法，所以在此限制只取第二层数据
                 WHERE DATA_MONTH='#V_DATA_MONTH#'
                  
      union all
      SELECT 
                            
                            BELONG_AREA_CODE,
                            CASE WHEN B.ORG_LVL_ID_2 IS NULL THEN '9999'
                                 WHEN B.ORG_LVL_ID_2 ='' THEN '9999'
                                 ELSE B.ORG_LVL_ID_2 END ORG_LVL_ID_2,
                            CUSTOMER_TYPE,
                            CASE WHEN C.DEPB_CATEGORY_ID_1 IS NULL THEN '9999' 
                                 WHEN C.DEPB_CATEGORY_ID_1 ='' THEN '9999'
                                 ELSE C.DEPB_CATEGORY_ID_1 END DEPB_CATEGORY_ID_1,
                            LOAN_PERIOD_TYPE_ID,
                            0 DEP_BALANCE,0 DEP_BALANCE_RATE,0 DEP_BALANCE_AVG,0 STROKE_COUNT,
                            0 DEP_AMOUNT,
                            0 DEP_AMOUNT_AMT, 
                            SUM(RATE_LEVEL) DEP_AMOUNT_RATE_SUM,
                            SUM(STROKE_COUNT) DEP_AMOUNT_STROKE_COUNT
                     FROM
                         EDW.DEPB A
                     LEFT JOIN DMCODE.T_ORG_BIZ_LVL B ON CASE WHEN a.DATA_ORG_CODE IS NULL THEN CONCAT('org_code_hive',RAND()) else A.DATA_ORG_CODE end= B.ORG_ID  ---金融机构
                     LEFT JOIN DMCODE.T_DEPB_CATEGORY C ON CASE WHEN a.PRODUCT_CATEGORY IS NULL THEN CONCAT('org_code_hive',RAND()) else A.PRODUCT_CATEGORY end= C.DEPB_CATEGORY_ID ---存款产品类别
                     WHERE
                            DATA_DATE ='#V_DATA_DATE#'  and ((PRODUCT_CATEGORY = 'D012' OR PRODUCT_CATEGORY = 'D014')
                      AND concat(substr(DEP_AGREEMENT_SDATE,1,4),substr(DEP_AGREEMENT_SDATE,6,2)) =  '#V_DATA_MONTH#'
                    or  PRODUCT_CATEGORY = 'D011'
                    or  PRODUCT_CATEGORY = 'D013')
                     GROUP BY
                            
                            BELONG_AREA_CODE,
                            CASE WHEN B.ORG_LVL_ID_2 IS NULL THEN '9999'
                                 WHEN B.ORG_LVL_ID_2 ='' THEN '9999'
                                 ELSE B.ORG_LVL_ID_2 END ,
                            CUSTOMER_TYPE,
                            CASE WHEN C.DEPB_CATEGORY_ID_1 IS NULL THEN '9999' 
                                 WHEN C.DEPB_CATEGORY_ID_1 ='' THEN '9999'
                                 ELSE C.DEPB_CATEGORY_ID_1 END ,
                            LOAN_PERIOD_TYPE_ID 
      
                    ) T1
        GROUP BY
            T1.BELONG_AREA_CODE,
            T1.ORG_LVL_ID_2,
            T1.CUSTOMER_TYPE,
            T1.DEPB_CATEGORY_ID_1,
            T1.LOAN_PERIOD_TYPE_ID 
            
            union all
            
            SELECT
            AREA_NO_ID                    BELONG_AREA_CODE,
            ORG_ID                        ORG_LVL_ID_2,
            CUSTOMER_TYPE                 CUSTOMER_TYPE,
            PRODUCT_CATEGORY              DEPB_CATEGORY_ID_1,
            DEP_PERIOD                    LOAN_PERIOD_TYPE_ID,
            0 DEP_BALANCE,0 DEP_BALANCE_RATE,
            SUM(DEP_BALANCE)              DEP_BALANCE_LM,
            SUM(DEP_BALANCE_AMT)          DEP_BALANCE_RATE_LM,
            0 DEP_BALANCE_AVG,0 STROKE_COUNT,
            SUM(DEP_BALANCE_RATE_SUM)     DEP_BALANCE_RATE_SUM_L,
            SUM(DEP_BALANCE_STROKE_COUNT) DEP_BALANCE_STROKE_COUNT_L,
            0 DEP_AMOUNTDEP_AMOUNT, 0 DEP_AMOUNT_AMT,
            SUM(DEP_AMOUNT)               DEP_MTH_LOAN_AMOUNT,
            SUM(DEP_AMOUNT_AMT)           DEP_MTH_LOAN_AMOUNT_AMT,
            0 DEP_AMOUNT_RATE_SUM,0 DEP_AMOUNT_STROKE_COUNT,
            SUM(DEP_AMOUNT_RATE_SUM)      DEP_AMOUNT_RATE_SUM_L,
            SUM(DEP_AMOUNT_STROKE_COUNT)  DEP_AMOUNT_STROKE_COUNT_L
        FROM
            DM.T_MTH_DEP_RATE
        WHERE
            FISCAL_MTH_DIMNSN_ID ='#V_LAST_1M_END_MONTH#'
        GROUP BY
            AREA_NO_ID,
            ORG_ID,
            CUSTOMER_TYPE,
            PRODUCT_CATEGORY,
            DEP_PERIOD )T1 
            GROUP BY 
        T1.BELONG_AREA_CODE,
        T1.ORG_LVL_ID_2,
        T1.CUSTOMER_TYPE,
        T1.DEPB_CATEGORY_ID_1,
        T1.LOAN_PERIOD_TYPE_ID
;